﻿using System;
using System.Web;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using OpenXMLExtensions;
using OpenXMLHelper;
using HC = ExcelExport.HelperClasses;

namespace ExcelExport
{
	public class ExportHelper
	{
		/// <summary>
		/// Generates formatted Excel export for one sheet
		/// </summary>
		/// <param name="ws">Worksheet</param>
		/// <returns>Export</returns>
		public static MemoryStream GenerateExcel(HC.Worksheet ws)
		{
			List<HC.Worksheet> worksheets = new List<HC.Worksheet>();
			worksheets.Add(ws);

			return GenerateExcel(worksheets, 1);
		}

		/// <summary>
		/// Generates formatted Excel export for up to 3 sheets
		/// </summary>
		/// <param name="worksheets">a List of Worksheets</param>
		/// <param name="nbrSheets">How many sheets to export</param>
		/// <returns>Export</returns>
		public static MemoryStream GenerateExcel(List<HC.Worksheet> worksheets, int nbrSheets)
		{
			// create the empty spreadsheet template 
			// using the class generated by the Productivity tool. 
			// it has been modified so don't go messing with it.
			// saves it in a memory stream so don't have mess with saving a file.

			MemoryStream excelMS;
			ExcelDocument excelDocument = new ExcelDocument();
			excelMS = excelDocument.CreatePackage(nbrSheets);

			// Open saved excel file created using template file.
			using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(excelMS, true))
			{
				// Create reference of main Workbook part, which contains all reference.
				WorkbookPart workbookPart = ssd.WorkbookPart;

				// created the Excel document (ExcelDocument.cs) via the reflection tool.
				// need to get a reverse list of the WorkSheetParts so using List
				List<WorksheetPart> worksheetParts = new List<WorksheetPart>();
				foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
				{
					worksheetParts.Add(wsp);
				}

				// need the Sheets so can populate them.  
				List<Sheet> sheets = new List<Sheet>();
				foreach (Sheet s in workbookPart.Workbook.Sheets)
				{
					sheets.Add(s);
				}

				// Create style sheet object that will be used for applying styling.
				Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;

				int wspCounter = worksheetParts.Count - 1;	// count backward
				int sheetCounter = 0;						// count forward.  who thinks this stuff up????

				// loop through each worksheet and populate
				foreach (HC.Worksheet ws in worksheets)
				{
					WorksheetPart worksheetPart = worksheetParts[wspCounter--];
					SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
					Columns worksheetColumns = worksheetPart.Worksheet.GetFirstChild<Columns>();
					SheetView sheetView = worksheetPart.Worksheet.GetFirstChild<SheetViews>().GetFirstChild<SheetView>();
					Sheet sheet = sheets[sheetCounter++];

					// format worksheet
					if (!string.IsNullOrEmpty(ws.SheetName))
						sheet.Name = ws.SheetName;

					if (!string.IsNullOrEmpty(ws.FreezePaneTopLeftCell))
					{
						Pane pane = OpenXML.CreateFreezePane(ws.FreezePaneTopLeftCell);
						sheetView.Append(pane);
					}

					int columnIndex = 1;		// Column A = 1
					Cell c;

					if (ws.Columns == null)
						worksheetColumns.Remove();		// no columns
					else
					{
						// columns
						foreach (Column column in ws.Columns)
						{
							worksheetColumns.Append(column);
						}
					}

					// header row
					Row headerRow = new Row();
					UInt32 rowIndex = 1;
					headerRow.RowIndex = rowIndex;

					if (ws.ColumnHeadings != null)
					{
						// Loop to insert header columns
						foreach (HC.Cell headerCols in ws.ColumnHeadings)
						{
							c = CreateCell(styleSheet, headerCols, columnIndex, rowIndex);
							headerRow.AppendChild(c);
							columnIndex += 1;
						}

						sheetData.AppendChild(headerRow);

						rowIndex += 1;
					}

					if (ws.Rows != null)
					{
						//Loop to insert data rows	
						foreach (HC.Row dataRow in ws.Rows)
						{
							Row contentRow = new Row();
							contentRow.RowIndex = rowIndex;

							columnIndex = 1;		// Column A = 1
							foreach (HC.Cell dataCols in dataRow.Cells)
							{
								c = CreateCell(styleSheet, dataCols, columnIndex, rowIndex);
								contentRow.AppendChild(c);
								columnIndex += 1;
							}

							sheetData.AppendChild(contentRow);
							rowIndex += 1;
						}
					}
				}

				ssd.Close();
			}

			return excelMS;
		}

		/// <summary>
		/// Generates unformatted Excel export for one sheet from a data table
		/// </summary>
		/// <param name="dt">Data table to export</param>
		/// <returns>Export</returns>
		public static MemoryStream GenerateExcel(DataTable dt)
		{
			List<HC.Worksheet> worksheets = new List<HC.Worksheet>();

			HC.Worksheet ws = PopulateSpreadsheetAttributes(dt);
			worksheets.Add(ws);

			return GenerateExcel(worksheets, 1);
		}

		/// <summary>
		/// Create a cell with styling elements
		/// </summary>
		/// <param name="styleSheet">stylesheet to append to</param>
		/// <param name="cell">data cell to create</param>
		/// <param name="columnIndex">column index of spreadsheet</param>
		/// <param name="rowIndex">row index of spreadsheet</param>
		/// <returns>openxml cell</returns>
		private static Cell CreateCell(	Stylesheet styleSheet,
										HC.Cell cell,
										int columnIndex,
										UInt32 rowIndex)
		{
			Cell xmlCell;
			UInt32Value cellFormatIndex = null;

			if (cell.HasFormatting())
			{
				CellFormat cellFormat = OpenXML.CreateCellFormat(cell, styleSheet);
				cellFormatIndex = cellFormat.GetCellFormatIndex(styleSheet);
			}

			xmlCell = CreateCell(cell, columnIndex, rowIndex);
			xmlCell.StyleIndex = cellFormatIndex;

			return xmlCell;
		}

		/// <summary>
		/// Create a cell with no styling elements
		/// </summary>
		/// <param name="cell">data cell to create</param>
		/// <param name="columnIndex">column index of spreadsheet</param>
		/// <param name="rowIndex">row index of spreadsheet</param>
		/// <returns>openxml cell</returns>
		private static Cell CreateCell(HC.Cell cell,
										int columnIndex,
										UInt32 rowIndex)
		{
			Cell xmlCell;

			if (cell.CellDataType == CellValues.InlineString)
				xmlCell = CreateInlineStringCell(cell.Value, columnIndex, rowIndex);
			else
			{
				xmlCell = new Cell();
				xmlCell.CellReference = GetColumnName(columnIndex) + Convert.ToString(rowIndex);
				if (cell.CellDataType != CellValues.Date)
					xmlCell.DataType = cell.CellDataType;

				CellValue cv = new CellValue(cell.Value);
				xmlCell.AppendChild(cv);
			}

			return xmlCell;
		}

		/// <summary>
		/// create a cell of inline string data type
		/// </summary>
		/// <param name="cellValue">string value</param>
		/// <param name="columnIndex">column index of spreadsheet</param>
		/// <param name="rowIndex">row index of spreadsheet</param>
		/// <returns>openxml cell</returns>
		private static Cell CreateInlineStringCell(string cellValue,
													int columnIndex,
													UInt32 rowIndex)
		{
			Cell cell = new Cell();
			cell.DataType = CellValues.InlineString;
			cell.CellReference = GetColumnName(columnIndex) + Convert.ToString(rowIndex);

			InlineString inlineString = new InlineString();
			Text t = new Text(cellValue);
			inlineString.AppendChild(t);
			cell.AppendChild(inlineString);

			return cell;
		}

		/// <summary>
		///  This gets the column name (ie. A, B, AA, etc...  A = 1 so NOT zero based)
		/// </summary>
		/// <param name="columnIndex">index to retrieve</param>
		/// <returns>excel name</returns>
		private static string GetColumnName(int columnIndex)
		{
			int dividend = columnIndex;
			string columnName = String.Empty;
			int modulo = 0;

			// loops backwards
			while (dividend > 0)
			{
				modulo = (dividend - 1) % 26;											//twenty-six letters in the alphabet
				columnName = Convert.ToChar(65 + modulo).ToString() + columnName;		//Append letters until we have accounted for all digits
				dividend = (int)((dividend - modulo) / 26);								//Get the remainder
			}

			return columnName;
		}

		/// <summary>
		/// Populates spreadsheet attributes for generic exporting
		/// </summary>
		/// <param name="dt">data table to export</param>
		/// <returns>worksheet with no formatting</returns>
		private static HC.Worksheet PopulateSpreadsheetAttributes(DataTable dt)
		{
			HC.Worksheet ws = new HC.Worksheet();

			// set sheet name as data table name
			if (string.IsNullOrEmpty(dt.TableName))
				ws.SheetName = "Data Table";
			else
				ws.SheetName = dt.TableName;

			// no frozen panes
			ws.FreezePaneTopLeftCell = "";

			List<HC.Cell> columnHeadings = new List<HC.Cell>();
			List<DataColumn> dataColumns = new List<DataColumn>();

			// create header 
			UInt32Value index = 0;
			foreach (DataColumn column in dt.Columns)
			{
				// save data column info
				dataColumns.Add(column);

				HC.Cell cell = new HC.Cell();
				cell.Value = column.ColumnName;
				cell.CellDataType = CellValues.InlineString;

				cell.Font = OpenXML.CreateNewFont("", null, true, false, "");
				cell.Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center };

				columnHeadings.Add(cell);

				index++;
			}

			ws.ColumnHeadings = columnHeadings;

			List<HC.Row> rows = new List<HC.Row>();

			// create data rows
			foreach (DataRow dr in dt.Rows)
			{
				HC.Row row = new HC.Row();
				List<HC.Cell> cells = new List<HC.Cell>();
				foreach (DataColumn column in dataColumns)
				{
					HC.Cell cell = new HC.Cell(column, dr);
					cells.Add(cell);
				}
				row.Cells = cells;
				rows.Add(row);
			}

			ws.Rows = rows;

			return ws;
		}
	}
}
